# 数据分析题目解答(建议先赞后看，养成习惯 如果不赞，先拉出去枪毙两分钟 作者：小匠IT)
import pandas as pd
import matplotlib.pyplot as plt
from matplotlib.font_manager import FontProperties
import os
from openpyxl import load_workbook
from openpyxl.drawing.image import Image

# 设置中文字体路径（请根据实际路径调整）
font_path = r'fonts/SIMSUN.TTC'
font_prop = FontProperties(fname=font_path)

# 创建输出文件夹
output_folder = r'output/42'
os.makedirs(output_folder, exist_ok=True)

# 给定的数据
data = {
    '统计周期': ['2021-11-08 ~ 2021-12-07'] * 10,
    '产品名称': [
        '原牧纯品绿鸟乌骨鸡700g 冷冻生鲜黑乌鸡炖汤滋补（赠美味汤包）',
        '原牧纯品绿鸟童子鸡600g生鲜冷冻农家散养鸡整只（赠美味骨汤包）',
        '原牧纯品绿鸟汤鸡800g 冷冻鸡肉农家散养鸡炖汤（赠美味骨汤包）',
        '原牧纯品绿鸟牧鸡900g生鲜冷冻鸡肉整只红烧煲汤（赠美味骨汤包）',
        '原牧纯品绿鸟冷冻鸡腿琵琶腿500g 清真鸡大腿烧烤炸鸡腿正品',
        '原牧纯品绿鸟鸡小胸500g 生鲜冷冻鸡胸肉烤肉食材鸡脯肉正品',
        '原牧纯品绿鸟鸡翅中500g 冷冻生制品生鸡翅烧烤烤肉食材正品',
        '原牧纯品绿鸟鸡翅根500g/袋 生鲜冷冻生鸡肉清真烧烤食材正品',
        '原牧纯品老母鸡1300g  冷冻散养土鸡走地老母鸡 清真烤鸡正品',
        '原牧纯品草原鸡1000g 冷冻鸡肉草原农家散养土鸡绿色健康正品'
    ],
    '展现量': [175103, 409028, 170578, 279855, 335726, 430683, 454386, 311190, 43954, 97588],
    '新客点击量': [6096, 12561, 5919, 6921, 7307, 10278, 11742, 9819, 1498, 3644],
    '支付买家数': [206, 316, 207, 231, 298, 303, 396, 302, 69, 142],
    '复购客户数': [14, 21, 7, 9, 11, 8, 21, 26, 3, 4]
}

# 创建 DataFrame
df_products = pd.DataFrame(data)

# 计算新客点击率、复购率和综合得分
df_products['新客点击率'] = (df_products['新客点击量'] / df_products['展现量']).round(4) * 100
df_products['复购率'] = (df_products['复购客户数'] / df_products['支付买家数']).round(4) * 100
df_products['综合得分'] = (df_products['新客点击率'] * 0.7 + df_products['复购率'] * 0.3).round(4)

# 将数值格式化为百分比或保留四位小数
df_products['新客点击率'] = df_products['新客点击率'].apply(lambda x: f'{x:.2f}%')
df_products['复购率'] = df_products['复购率'].apply(lambda x: f'{x:.2f}%')
df_products['综合得分'] = df_products['综合得分'].apply(lambda x: f'{x:.4f}')

# 准备绘图数据
plot_data = df_products[['产品名称', '新客点击率', '复购率', '综合得分']].copy()
plot_data['新客点击率'] = plot_data['新客点击率'].str.rstrip('%').astype('float') / 100.0
plot_data['复购率'] = plot_data['复购率'].str.rstrip('%').astype('float') / 100.0
plot_data['综合得分'] = plot_data['综合得分'].astype('float')

# 生成图表：产品获客能力分析
fig, ax = plt.subplots(figsize=(14, 7))
bar_width = 0.2
index = range(len(df_products))

for i, col in enumerate(['新客点击率', '复购率', '综合得分']):
    bars = ax.bar([p + i * bar_width for p in index], plot_data[col], bar_width, label=col)

ax.set_xlabel('产品名称', fontproperties=font_prop, fontsize=14)
ax.set_ylabel('比率', fontproperties=font_prop, fontsize=14)
ax.set_title('产品获客能力分析', fontproperties=font_prop, fontsize=16)
ax.set_xticks([p + bar_width for p in index])
ax.set_xticklabels(df_products['产品名称'], rotation=45, ha='right', fontproperties=font_prop)
ax.legend(prop=font_prop)
plt.tight_layout()

output_image_customer_acquisition = os.path.join(output_folder, '产品获客能力分析.png')
plt.savefig(output_image_customer_acquisition, dpi=300, bbox_inches='tight')
plt.close()
print(f"产品获客能力分析图已保存到 {output_image_customer_acquisition}")

# 创建新的 Excel 文件并插入数据和图表
output_excel_file = os.path.join(output_folder, '产品获客能力分析.xlsx')

with pd.ExcelWriter(output_excel_file, engine='xlsxwriter') as writer:
    # 将原始数据写入 Excel 文件
    df_products.to_excel(writer, sheet_name='产品获客能力', index=False)
    
    # 插入图表到 Excel 工作表中
    workbook = writer.book
    worksheet = writer.sheets['产品获客能力']
    
    # 插入图片到指定位置
    worksheet.insert_image('J2', output_image_customer_acquisition)

print(f"包含图表的新 Excel 文件已保存到 {output_excel_file}")